/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Server;

import Model.Book;
import Model.IssueNote;
import Server.MyConnect;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author Admin
 */
public class ServerIssue {

    ObjectInputStream inObject;
    ObjectOutputStream outObject;
    BufferedReader inString;
    PrintWriter outString;

    public ServerIssue(ObjectInputStream inObject, ObjectOutputStream outObject) {
        this.inObject = inObject;
        this.outObject = outObject;
    }

    private ServerIssue() {
    }

    public boolean CheckReaderBorrowBook(String ReaderID) {//Kiem tra ma nhan vien nay co du dieu kien de muon sach khong
        Connection con = MyConnect.getConnect();
        try {
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery("select AmountBookIssued from Readers where ReaderID='" + ReaderID + "'");
            if (rs.next()) {//Kiem tra xem nguoi nhan vien nay co ton tai hay khong
                if (rs.getInt(1) >= 3) {//Kiem tra xem nhan vien nay da muon 3 quen sach chua
                    outObject.writeObject("The Reader has borrowed three books");
                    return false;
                } else {
                    return true;
                }
            } else {
                outObject.writeObject("No information about this Reader");
                return false;
            }
        } catch (Exception e) {
            try {
                outObject.writeObject("Error when check Reader");
                e.printStackTrace();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
            return false;
        }
    }

    public void IncOrDecAmountBookIssued(String ReaderID, int i) {//Sau khi cho nhan vien muon sach thi phai tang so sach muon nhan vien la 3
        //i = 1 la tang, i = 2 la giam
        Connection con = MyConnect.getConnect();
        String sql = "select AmountBookIssued from Readers where ReaderID='" + ReaderID + "'";
        String sql1 = "update Readers set AmountBookIssued=? where ReaderID='" + ReaderID + "'";
        try {
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            rs.next();
            int Amount = rs.getInt(1);
            if (i == 1) {
                Amount++;
            } else if (i == 2) {
                Amount--;
            }
            PreparedStatement pst = con.prepareStatement(sql1);
            pst.setInt(1, Amount);
            pst.executeUpdate();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "IncOrDecAmountBookIssued: " + e.getMessage());
        }


    }

    public void ChangeBookActive(String BookID) {
        //i = 1 la tang, i = 2 la giam
        Connection con = MyConnect.getConnect();
        String sql = "select Active from Book where BookID='" + BookID + "'";
        String sql1 = "update Book set Active=? where BookID='" + BookID + "'";
        try {
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            rs.next();
            boolean Active = rs.getBoolean(1);
            PreparedStatement pst = con.prepareStatement(sql1);
            if (Active) {
                Active = false;
            } else {
                Active = true;
            }
            pst.setBoolean(1, Active);
            pst.executeUpdate();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "ChangeBookActive: " + e.getMessage());
        }


    }

    public void controlBorrow() {//Dieu khien viec muon sach
        try {
            String ReaderID = (String) inObject.readObject();
            String BookID = (String) inObject.readObject();
            if (BorrowBook(ReaderID, BookID)) {
                outObject.writeObject("Borrow Book Completed");
            }

        } catch (Exception ex) {
            try {
                outObject.writeObject("Error Borrow Book");
            } catch (IOException ex1) {
                ex.printStackTrace();
            }
            ex.printStackTrace();
        }

    }

    public boolean BorrowBook(String ReaderID, String BookID) {
        Connection con = MyConnect.getConnect();
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/YYYY");
        String NowDate = sdf.format(cal.getTime());//Lay ngay hien tai  
        cal.add(Calendar.DAY_OF_MONTH, 7);//Tang ngay hien tai len 7 ngay
        String Due = sdf.format(cal.getTime());//Lay ngay da tang
        try {
            String sql = "insert into IssueNote(ReaderID,BookID,DueDate,IssueDate) values(?,?,?,?)";
            PreparedStatement pst = con.prepareStatement(sql);
            pst.setString(1, ReaderID);
            pst.setString(2, BookID);
            pst.setString(3, Due);
            pst.setString(4, NowDate);
            if (CheckReaderBorrowBook(ReaderID)) {
                pst.executeUpdate(); //Sau khi cho muon sach thi phai tang gia tri AmountBookIssued cua nhan vien len 1
                //Set active cua quyen sach do lai bang false;
                IncOrDecAmountBookIssued(ReaderID, 1);
                ChangeBookActive(BookID);
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

    }

    public void CheckFine() {
        try {
            //Tinh tien phat gui ve client
            int IssueID = (int) inObject.readObject();
            int AmountDateLate = CompareReturnDay(IssueID);
            if (AmountDateLate == -1) {
                outObject.writeObject("Have error when check fine, please try again");
            } else {
                outObject.writeObject(AmountDateLate * 10000 + "");//Phat 10000 tren mot ngay
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public int CompareReturnDay(int IssueID) {//So sanh ngay tra voi duedate de tinh phi phat
        Calendar c1 = Calendar.getInstance();
        Calendar c2 = Calendar.getInstance();
        String sDueDate;
        try {   //Tim gia tri DueDate tu BookID
            Connection con = MyConnect.getConnect();
            Statement stm = con.createStatement();
            String sql = "select DueDate from IssueNote where IssueID='" + IssueID + "'";
            ResultSet rs = stm.executeQuery(sql);
            rs.next();
            sDueDate = rs.getString(1);
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
        String[] duedate = sDueDate.split("-");
        if (duedate.length == 3) {
            c1.set(Calendar.MONTH, Integer.parseInt(duedate[1]) - 1);
            c1.set(Calendar.DAY_OF_MONTH, Integer.parseInt(duedate[2]));
            c1.set(Calendar.YEAR, Integer.parseInt(duedate[0]));
            int dateFine;
            dateFine = (int) ((c2.getTime().getTime() - c1.getTime().getTime()) / (24 * 3600 * 1000));
            if (dateFine > 0) {
                return dateFine;//New dateFine lon hon khong thi se phat
            } else {
                return 0;
            }
        } else {
            JOptionPane.showMessageDialog(null, "Format date no correct");
            return -1;
        }

    }

    public void controlReturnBook() {
        try {
            int IssueID = (int) inObject.readObject();
            String BookID = (String) inObject.readObject();
            String ReaderID = (String) inObject.readObject();
            if (ReturnBook(IssueID, BookID, ReaderID)) {
                outObject.writeObject("Return book completed");
            } else {
                outObject.writeObject("Return book not completed");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public boolean ReturnBook(int IssueID, String BookID, String ReaderID) {
        Connection con = MyConnect.getConnect();
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/YYYY");
        String NowDate = sdf.format(cal.getTime());//Lay ngay hien tai  
        String sql = "update IssueNote set ReturnDate=?, Fine=?, Note=? where IssueID='" + IssueID + "'";
        try {
            PreparedStatement pst = con.prepareStatement(sql);
            pst.setString(1, NowDate);
            int compareDay = CompareReturnDay(IssueID);
            pst.setInt(2, compareDay * 10000);//Muon 1 ngay phat 10000
            if (compareDay > 0) {
                pst.setString(3, "Return late");
            } else {
                pst.setString(3, "");
            }
            pst.executeUpdate();
            //GIam so sach da muon cua nhan vien nay xuong 1
            //Quyen sach nay dc set lai gia tri Active;
            IncOrDecAmountBookIssued(ReaderID, 2);//Neu tra sach thanh cong thi giam so sach muon cho Reader
            ChangeBookActive(BookID);//Thay doi gia tri active cho sach 
            return true;
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "ReturnBook: " + e.getMessage());
            return false;
        }
    }

    public void ControlSearchIssue() {//Tim kiem cho ca phan Return va phan Report        
        try {
            String keyWord = (String) inObject.readObject();
            String where = (String) inObject.readObject();
            int i = (int) inObject.readObject();
            ArrayList<IssueNote> arrIss = new ArrayList<>();
            arrIss = SearchIssue(keyWord, where, i);
            outObject.writeObject(arrIss);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public void ControlSearchTitle() {//Tim kiem cho ca phan Return va phan Report        
        try {
            String keyWord = (String) inObject.readObject();
            int i = (int) inObject.readObject();
            ArrayList<IssueNote> arrIss = new ArrayList<>();
            arrIss = SearchIssueTitle(keyWord, i);
            outObject.writeObject(arrIss);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public void ControlSearchFullName() {//Tim kiem cho ca phan Return va phan Report        
        try {
            String keyWord = (String) inObject.readObject();
            int i = (int) inObject.readObject();
            ArrayList<IssueNote> arrIss = new ArrayList<>();
            arrIss = SearchIssueFullName(keyWord, i);
            outObject.writeObject(arrIss);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public ArrayList<IssueNote> SearchIssueTitle(String keyWord, int i) {

        //Tu khoa tim kiem, thuoc tinh cua tu khoa, co hien nhung phieu da tra hay khong?
        //1 la hien va 0 la khong hien
        Connection con = MyConnect.getConnect();
        ArrayList<IssueNote> aIss = new ArrayList<IssueNote>();
        try {
            Statement stm = con.createStatement();
            ResultSet rs = null;
            String sqlBookID = "select * from IssueNote,Book where Book.Title like ('%" + keyWord + "%') and Book.BookID=IssueNote.BookID";
            rs = stm.executeQuery(sqlBookID);
            while (rs.next()) {
                IssueNote iss = new IssueNote();
                iss.IssueID = rs.getInt(1);
                iss.ReaderID = rs.getString(2);
                iss.BookID = rs.getString(3);
                iss.DueDate = rs.getString(4);
                iss.IssueDate = rs.getString(5);
                iss.ReturnDate = rs.getString(6);
                iss.Fine = rs.getInt(7);
                iss.Note = rs.getString(8);
                if (i == 0) {
                    if (iss.ReturnDate == null) {
                        aIss.add(iss);
                    }
                } else {
                    aIss.add(iss);
                }
            }
            return aIss;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public ArrayList<IssueNote> SearchIssueFullName(String keyWord, int i) {

        //Tu khoa tim kiem, thuoc tinh cua tu khoa, co hien nhung phieu da tra hay khong?
        //1 la hien va 0 la khong hien
        Connection con = MyConnect.getConnect();
        ArrayList<IssueNote> aIss = new ArrayList<IssueNote>();
        try {
            Statement stm = con.createStatement();
            ResultSet rs = null;
            String sqlBookID = "select * from IssueNote,Readers where Readers.FullName like ('%" + keyWord + "%') and Readers.ReaderID=IssueNote.ReaderID";
            rs = stm.executeQuery(sqlBookID);
            while (rs.next()) {
                IssueNote iss = new IssueNote();
                iss.IssueID = rs.getInt(1);
                iss.ReaderID = rs.getString(2);
                iss.BookID = rs.getString(3);
                iss.DueDate = rs.getString(4);
                iss.IssueDate = rs.getString(5);
                iss.ReturnDate = rs.getString(6);
                iss.Fine = rs.getInt(7);
                iss.Note = rs.getString(8);
                if (i == 0) {
                    if (iss.ReturnDate == null) {
                        aIss.add(iss);
                    }
                } else {
                    aIss.add(iss);
                }
            }
            return aIss;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public ArrayList<IssueNote> SearchIssue(String keyWord, String where, int i) {

        //Tu khoa tim kiem, thuoc tinh cua tu khoa, co hien nhung phieu da tra hay khong?
        //1 la hien va 0 la khong hien
        Connection con = MyConnect.getConnect();
        ArrayList<IssueNote> aIss = new ArrayList<IssueNote>();
        try {
            Statement stm = con.createStatement();
            ResultSet rs = null;
            String sqlBookID = "select * from IssueNote where " + where + " ='" + keyWord + "'";
            rs = stm.executeQuery(sqlBookID);
            while (rs.next()) {
                IssueNote iss = new IssueNote();
                iss.IssueID = rs.getInt(1);
                iss.ReaderID = rs.getString(2);
                iss.BookID = rs.getString(3);
                iss.DueDate = rs.getString(4);
                iss.IssueDate = rs.getString(5);
                iss.ReturnDate = rs.getString(6);
                iss.Fine = rs.getInt(7);
                iss.Note = rs.getString(8);
                if (i == 0) {
                    if (iss.ReturnDate == null) {
                        aIss.add(iss);
                    }
                } else {
                    aIss.add(iss);
                }
            }
            return aIss;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public void ControlShowAllIssueNote(int i) {//Dieu khien viec hien thi danh sach cac phieu muon
        //i the hien co hien nhung phieu tra roi hay khong
        //1 la hien, 0 la khong hien
        ArrayList<IssueNote> arrIss = new ArrayList<>();
        arrIss = ShowAllIssueNote(i);
        if (arrIss == null) {
            try {
                outObject.writeObject(null);
            } catch (IOException ex) {
                ex.printStackTrace();
            }
            return;
        } else {
            try {
                outObject.writeObject(arrIss);
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }

    private ArrayList<IssueNote> ShowAllIssueNote(int i) {
        //i the hien co hien nhung phieu tra roi hay khong
        //1 la hien, 0 la khong hien
        ArrayList<IssueNote> aIss = new ArrayList<IssueNote>();
        Connection con = MyConnect.getConnect();
        String sql = "select * from IssueNote";
        try {
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                IssueNote iss = new IssueNote();
                iss.IssueID = rs.getInt(1);
                iss.ReaderID = rs.getString(2);
                iss.BookID = rs.getString(3);
                iss.DueDate = rs.getString(4);
                iss.IssueDate = rs.getString(5);
                iss.ReturnDate = rs.getString(6);
                iss.Fine = rs.getInt(7);
                iss.Note = rs.getString(8);
                if (i == 1) {
                    aIss.add(iss);
                } else {
                    if (iss.ReturnDate == null) {
                        aIss.add(iss);
                    }
                }
            }
            return aIss;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }


    }

   /* public void returnTitle() {
        try {
            String BookID = (String) inObject.readObject();
            Connection con = MyConnect.getConnect();
            String sql = "select Title from Book where BookID='" + BookID + "'";
            String s;
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            if (rs.next()) {
                s = rs.getString(1);
                outObject.writeObject(s);
            }            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public void returnFullName() {
        try {
            String ReaderID = (String) inObject.readObject();
            Connection con = MyConnect.getConnect();
            String sql = "select Title from Readers where ReaderID='" + ReaderID + "'";
            String s;
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            if (rs.next()) {
                s = rs.getString(1);
                outObject.writeObject(s);
            }            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        ServerIssue iss = new ServerIssue();
        ArrayList<IssueNote> arrIss = new ArrayList<>();
        arrIss = iss.SearchIssue("re002", "ReaderID", 1);
        for (IssueNote i : arrIss) {
            System.out.println(i);
        }
    }*/
}
